package com.sinodata.bsm.cicp.util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Map;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;

import com.sinodata.bsm.common.vo.ProtocolParameter;

/**
 * 
 * <p>
 * Description: 
 * </p>
 *
 * @author liulibo
 * @version 1.0

 * <p>
 * History: 
 *
 * Date                     Author         Version     Description
 * ---------------------------------------------------------------------------------
 * 2012-5-18 PM  9:57:20    liulibo        1.0         To create
 * </p>
 *
 * @since 
 * @see
 */
public class DBConnectionUtil {

    private static final Logger logger = Logger.getLogger(DBConnectionUtil.class);

    private static DBConnectionUtil instance = null;

    private final Hashtable<String, BasicDataSource> pool = new Hashtable<String, BasicDataSource>();

    private final Hashtable<String, String> vSqls = new Hashtable<String, String>();

    private static Object _lock = new Object();

    private final Map<String, Boolean> connectStatusMap = new HashMap<String, Boolean>();

    /** Creates a new instance of DBConnectionUtil */
    private DBConnectionUtil() {
        Collections.synchronizedMap(connectStatusMap);
        //initDrivers(); probe启动时已经完成
        initDBValidateSQL();
    }

    /**
     * get single instance of DBConnectionUtil
     * 
     * @return DBConnectionUtil
     */
    public static DBConnectionUtil getInstance() {
        if (instance == null) {
            synchronized (_lock) {
                if (instance == null) {
                    instance = new DBConnectionUtil();
                }
            }
        }
        return instance;
    }

    /**
     * 设置数据库连接状态，如果数据库连接状态不可用，则关闭该数据库连接池
     * 
     * @param db
     * @param ip
     * @param svrName
     * @param enable
     */
    public void setConnectionEnable(String db, String ip, String svrName, boolean enable) {
        String key = db + ":" + svrName + ":" + ip;
        if (!connectStatusMap.containsKey(key)) {
            connectStatusMap.put(key, true);
        }
        boolean status = connectStatusMap.get(key);
        if (status != enable) {
            connectStatusMap.put(key, enable);
            BasicDataSource ds = pool.get(key);
            if (ds == null) {
                return;
            }
            if (!enable) {
                try {
                    ds.close();
                } catch (Exception e) {
                    logger.error(e);
                }
                pool.remove(key);
            }
        }
    }

    /**
     * 获取数据库连接，1.判断数据库连接是否可用，如果已经设置为不可用状态这直接返回null； 2.如果尚没有建立数据库连接池，这先建立连接池；
     * 3.从连接池中获取一个连接。
     * 
     * @param db
     * @param ip
     * @param svrName
     * @return
     * @throws SQLException
     * @throws IOException
     */
    public Connection getConnection(String db, String ip, String svrName) throws SQLException, IOException {
        String key = db + ":" + svrName + ":" + ip;
        Boolean status = connectStatusMap.get(key);
        if (status != null && !status) {
            return null;
        }
        BasicDataSource ds = pool.get(key);
        if (ds == null) {
            synchronized (this) {
                ds = pool.get(key);
                if (ds == null) {
                    ds = newDataSource(db, ip, svrName);
                    pool.put(key, ds);
                }
            }
        }
        Connection conn = null;
        try {
            conn = ds.getConnection();
            return conn;
        } catch (Exception e) {
            throw new SQLException("no idle connection: " + e.getLocalizedMessage());
        }
    }

    /**
     * 通过URL获得连接池
     * 
     * @param url
     *            String 连接数据库使用的URL
     * @param usr
     *            String 连接数据库使用的用户名
     * @param pwd
     *            String 连接数据库使用的密码
     * @return Connection 获得的连接
     * @throws SQLException
     *             捕获在获得连接过程中抛出的SQLException
     * @throws IOException
     *             捕获在获得连接过程中抛出的IOException
     * 
     *             Credible_zhang by 2006-01-17
     */
    @Deprecated
    public Connection getConnByURL(String url, String usr, String pwd) throws SQLException, IOException {
        BasicDataSource ds = pool.get(url);
        if (ds == null) {
            synchronized (this) {
                ds = pool.get(url);
                if (ds == null) {
                    ds = initDataSource(url, usr, pwd, null);
                    pool.put(url, ds);
                }
            }
        }
        Connection conn = ds.getConnection();
        if (conn == null) {
            throw new SQLException("no idle connection");
        } else {
            return conn;
        }
    }

    /**
     * 归还连接
     * 
     * @param conn
     *            Connection
     */
    public void returnConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
            }
        }
    }

    /**
     * 创建数据库连接
     * 
     * @param db
     * @param ip
     * @param dbName
     * @return
     * @throws SQLException
     * @throws IOException
     */
    private BasicDataSource newDataSource(String db, String ip, String dbName) throws SQLException, IOException {
        ProtocolParameter protocolParameter = ProtocolParameterUtil.getJdbcValue(ip, db, dbName);
        String url = protocolParameter.getUrl();
        Integer port = protocolParameter.getPort();
        String usr = protocolParameter.getUserName();
        String pwd = protocolParameter.getPwd();
        url = getDbUrl(url, ip, dbName, String.valueOf(port), usr, pwd);
        return initDataSource(url, usr, pwd, db);
    }

    /**
     * 初始化数据库连接
     * 
     * @param url
     * @param usrname
     * @param pwd
     * @param db
     * @return
     * @throws SQLException
     * @throws IOException
     */
    public BasicDataSource initDataSource(String url, String usrname, String pwd, String db) throws SQLException, IOException {
        logger.info("Create new DB connection:" + url);
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName(DriverManager.getDriver(url).getClass().getName());
        ds.setUsername(usrname);
        ds.setPassword(pwd);
        ds.setUrl(url);
        ds.setMaxActive(2);
        ds.setMaxIdle(2);
        ds.setMinIdle(1);
        ds.setMaxWait(60 * 60 * 1000);
        String vsql = vSqls.get(db);
        if (vsql != null) {
            ds.setTestOnBorrow(true);
            ds.setTestOnReturn(true);
            ds.setTestWhileIdle(true);
            ds.setValidationQuery(vsql);
        }
        logger.info("Create new connection for " + db + " with url=" + url + ",validate sql=" + vsql);
        return ds;
    }

    /**
     * 初始化数据库验证SQL
     */
    private void initDBValidateSQL() {
        String fileName = "cicp-conf/sqlIdentify.properties";
        File file = null;
        InputStream inputStream = null;
        InputStreamReader inputStreamReader = null;
        BufferedReader reader = null;
        String readLine = null;
        try {
            file = new File(fileName);
            if (file == null || !file.exists()) {
                return;
            }
            inputStream = new FileInputStream(file);
            inputStreamReader = new InputStreamReader(inputStream);
            reader = new BufferedReader(inputStreamReader);
            while ((readLine = reader.readLine()) != null) {
                readLine = readLine.trim();
                if (readLine.startsWith("#") || readLine.equals("")) {
                    continue;
                }
                String[] sqls = readLine.split("=");
                if (sqls != null && sqls.length >= 2) {
                    vSqls.put(sqls[0], readLine.substring(readLine.indexOf("=") + 1));
                }
            }
        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (Exception e) {
                }
            }
            if (inputStreamReader != null) {
                try {
                    inputStreamReader.close();
                } catch (Exception e) {
                }
            }
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (Exception e) {
                }
            }
        }

    }

    /**
     * 初始化所有数据库驱动程序
     */
    //    private void initDrivers() {
    //        String fileName = "cicp-conf/dbdrivers.conf";
    //        File file = null;
    //        InputStream inputStream = null;
    //        InputStreamReader inputStreamReader = null;
    //        BufferedReader reader = null;
    //        String readLine = null;
    //        try {
    //            file = new File(fileName);
    //            if (file == null || !file.exists()) {
    //                return;
    //            }
    //            inputStream = new FileInputStream(file);
    //            inputStreamReader = new InputStreamReader(inputStream);
    //            reader = new BufferedReader(inputStreamReader);
    //            while ((readLine = reader.readLine()) != null) {
    //                readLine = readLine.trim();
    //                if (readLine.startsWith("#") || readLine.equals("")) {
    //                    continue;
    //                }
    //                try {
    //                    Class.forName(readLine);
    //                    logger.info("Success to load DB driver:" + readLine);
    //                } catch (Exception e) {
    //                    logger.error("Failed to load DB driver:" + readLine, e);
    //                }
    //            }
    //        } catch (Exception e) {
    //            logger.error(e);
    //        } finally {
    //            if (reader != null) {
    //                try {
    //                    reader.close();
    //                } catch (Exception e) {
    //                }
    //            }
    //            if (inputStreamReader != null) {
    //                try {
    //                    inputStreamReader.close();
    //                } catch (Exception e) {
    //                }
    //            }
    //            if (inputStream != null) {
    //                try {
    //                    inputStream.close();
    //                } catch (Exception e) {
    //                }
    //            }
    //        }
    //    }

    /**
     * 获取数据库连接串，替换占位符
     * 
     * @param url
     * @param ip
     * @param dbName
     * @param port
     * @param usr
     * @param pwd
     * @return
     */
    public String getDbUrl(String url, String ip, String dbName, String port, String usr, String pwd) {
        url = url.replace("$ip", ip);
        url = url.replace("$dbname", dbName);
        url = url.replace("$port", port);
        url = url.replace("$usr", usr);
        if (pwd != null) {
            url = url.replace("$pwd", pwd);
        }
        return url;
    }

}
